package com.yt.Insurance.server.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

@Component
public class DatabaseInitializer implements CommandLineRunner {

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Override
    public void run(String... args) throws Exception {
        // 提取基础URL（不包含数据库名）
        String baseUrl = dbUrl.contains("?") ? dbUrl.substring(0, dbUrl.indexOf("?")) : dbUrl;
        if (baseUrl.endsWith("/")) {
            baseUrl = baseUrl.substring(0, baseUrl.length() - 1);
        }
        
        // 移除可能的数据库名部分
        if (baseUrl.contains("/")) {
            baseUrl = baseUrl.substring(0, baseUrl.lastIndexOf("/"));
        }

        // 创建数据库连接URL（不指定数据库）
        String connectionUrl = baseUrl;

        try (Connection connection = DriverManager.getConnection(connectionUrl, username, password)) {
            Statement statement = connection.createStatement();
            
            // 创建数据库
            statement.executeUpdate("CREATE DATABASE IF NOT EXISTS yt_bx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
            System.out.println("Database 'yt_bx' created or already exists.");
            
            // 使用数据库
            statement.execute("USE yt_bx");
            
            // 创建user表（被保险人表）
            String createUserTableSQL = "CREATE TABLE IF NOT EXISTS `user` (" +
                    "`id` bigint NOT NULL AUTO_INCREMENT," +
                    "`account` varchar(50) NOT NULL," +
                    "`password` varchar(100) NOT NULL," +
                    "`salt` varchar(50) DEFAULT NULL," +
                    "`name` varchar(100) DEFAULT NULL," +
                    "`id_card` varchar(50) DEFAULT NULL," +
                    "`is_delete` int DEFAULT '0'," +
                    "`create_time` datetime DEFAULT NULL," +
                    "`update_time` datetime DEFAULT NULL," +
                    "PRIMARY KEY (`id`)," +
                    "UNIQUE KEY `uk_account` (`account`)" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
            
            statement.executeUpdate(createUserTableSQL);
            System.out.println("Table 'user' created or already exists.");
            
            // 检查insurance_policy表是否存在insurant_id字段，如果存在则进行重命名
            try {
                statement.execute("SELECT insurant_id FROM insurance_policy LIMIT 1");
                // 如果没有抛出异常，说明insurant_id字段存在，需要重命名为user_id
                System.out.println("Detected old table structure, updating column name from insurant_id to user_id");
                
                // 删除外键约束（如果存在）
                try {
                    statement.execute("ALTER TABLE insurance_policy DROP FOREIGN KEY insurance_policy_ibfk_1");
                    System.out.println("Dropped old foreign key constraint");
                } catch (SQLException e) {
                    System.out.println("No existing foreign key constraint to drop");
                }
                
                // 重命名字段并修改类型为bigint
                statement.execute("ALTER TABLE insurance_policy CHANGE insurant_id user_id BIGINT DEFAULT NULL");
                System.out.println("Renamed insurant_id column to user_id and changed type to BIGINT");
                
                // 重新创建外键约束
                statement.execute("ALTER TABLE insurance_policy ADD CONSTRAINT insurance_policy_ibfk_1 FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE RESTRICT");
                System.out.println("Added new foreign key constraint");
            } catch (SQLException e) {
                System.out.println("Table is already using user_id column or does not exist yet");
            }
            
            // 检查insurance_policy表中的user_id是否为int类型，如果是则修改为bigint
            try {
                // 尝试插入一个大数值来检测字段类型
                statement.execute("INSERT INTO insurance_policy (user_id) VALUES (1981249435352035330)");
                statement.execute("DELETE FROM insurance_policy WHERE user_id = 1981249435352035330");
            } catch (SQLException e) {
                if (e.getMessage().contains("Out of range")) {
                    System.out.println("Updating user_id column type from INT to BIGINT");
                    
                    // 删除外键约束（如果存在）
                    try {
                        statement.execute("ALTER TABLE insurance_policy DROP FOREIGN KEY insurance_policy_ibfk_1");
                        System.out.println("Dropped old foreign key constraint");
                    } catch (SQLException dropException) {
                        System.out.println("No existing foreign key constraint to drop");
                    }
                    
                    // 修改user_id字段类型为BIGINT
                    statement.execute("ALTER TABLE insurance_policy MODIFY user_id BIGINT DEFAULT NULL");
                    System.out.println("Modified user_id column type to BIGINT");
                    
                    // 重新创建外键约束
                    statement.execute("ALTER TABLE insurance_policy ADD CONSTRAINT insurance_policy_ibfk_1 FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE RESTRICT");
                    System.out.println("Added new foreign key constraint");
                }
            }
            
            // 创建insurance_policy表（如果不存在）
            String createTableSQL = "CREATE TABLE IF NOT EXISTS `insurance_policy` (" +
                    "`policy_id` int NOT NULL AUTO_INCREMENT," +
                    "`user_id` bigint DEFAULT NULL," +
                    "`product_id` int DEFAULT NULL," +
                    "`policy_no` varchar(50) DEFAULT NULL," +
                    "`beneficiary_name` varchar(100) DEFAULT NULL," +
                    "`beneficiary_idcard` varchar(50) DEFAULT NULL," +
                    "`beneficiary_relation` varchar(50) DEFAULT NULL," +
                    "`insurance_amount` decimal(10,2) DEFAULT NULL," +
                    "`insurance_term` int DEFAULT NULL," +
                    "`has_medical_history` tinyint DEFAULT NULL," +
                    "`medical_history_desc` varchar(500) DEFAULT NULL," +
                    "`status_attr_id` int DEFAULT NULL," +
                    "`reject_reason` varchar(500) DEFAULT NULL," +
                    "`insurance_time` datetime DEFAULT NULL," +
                    "`end_time` datetime DEFAULT NULL," +
                    "`surrender_time` datetime DEFAULT NULL," +
                    "`surrender_reason` varchar(500) DEFAULT NULL," +
                    "`refund_amount` decimal(10,2) DEFAULT NULL," +
                    "`create_time` datetime DEFAULT NULL," +
                    "`update_time` datetime DEFAULT NULL," +
                    "`is_delete` tinyint DEFAULT '0'," +
                    "PRIMARY KEY (`policy_id`)," +
                    "KEY `fk_user` (`user_id`)," +
                    "CONSTRAINT `insurance_policy_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
            
            statement.executeUpdate(createTableSQL);
            System.out.println("Table 'insurance_policy' created or already exists.");
            
        } catch (SQLException e) {
            System.err.println("Error initializing database: " + e.getMessage());
            e.printStackTrace();
        }
    }
}